import datetime
import time
import numpy as np
import pymysql 
import pandas as pd
import requests
from sklearn.preprocessing import MinMaxScaler
from joblib import dump


class WeatherUtils(object):
    """天气类"""
    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
        self.url = 'http://v1.yiketianqi.com/api'

    def get_data(self):
        """获取天气数据"""
        date_list = []
        for d in self.date_list:
            conf = {
                'appid': '19575368',  # 用自己注册的appid
                'appsecret': "OjPoY5zW",  # 用自己注册的appsecret
                'version': "history",
                'year': d[:4],
                'month': d[5:7],
                'city': "南昌"
            }
            # 发起请求获取数据
            res = requests.get(self.url, params=conf)
            res_data = res.json()
            for i in res_data['data']:
                date_list.append({
                    'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),
                    'bWendu': i['bWendu'],
                    'yWendu': i['yWendu'],
                    'tianqi': i['tianqi'],
                    'fengxiang': i['fengxiang'],
                    'fengli': i['fengli'],
                })
        df = pd.DataFrame(date_list)
        df.to_csv('timing/weather_data.csv', index=False)


class MysqlUtils(object):

    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host= '127.0.0.1',
            user= 'root',
            password= 'root',
            database= 'scenic',
            port= 3306,          # 明确指定端口
            charset= 'utf8mb4'   # 添加字符集设置
        )
        self.weather_df = pd.read_csv("timing/weather_data.csv")
        
    def is_holiday(self, date):
        """是否节假日判断
        """
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:
            return 1
        return 0
        
    # def get_data(self):
    #     cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    #     sql = """
    #     SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count 
    #     FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
    #     """
    #     cursor.execute(sql)
    #     ret = cursor.fetchall()
    #     df = pd.DataFrame(ret)
    #     # print(df)
    #     # 格式转换
    #     date_range = pd.date_range(start='2024-07-01', end='2025-01-01', freq='D')
    #     hours = range(6, 24)
    #     full_index = pd.MultiIndex.from_product([date_range, hours], names=['date', 'hour'])
    #     df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index()
    #     # 按天组织数据，每行包含18个小时的检票次数
    #     df_pivot = df_full.pivot(index='date', columns='hour', values='count')
    #     # print(df_pivot.head)
    #     df_pivot['dow'] = df_pivot.index.dayofweek # 星期几（0-6）
    #     df_pivot['month'] = df_pivot.index.month # 月份
    #     df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
    #     # print(df_pivot.head)
        
    #     # 对星期几和月份进行独热编码
    #     df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month'], dtype=int)
        
    #     # 归一化小时检票列
    #     hours_columns = list(range(6, 24))
    #     df_hours = df_pivot[hours_columns].copy()
        
    #     feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
    #     df_feature = df_pivot[feature_columns].copy()
        
    #     scaler = MinMaxScaler()
    #     scaled_hours = scaler.fit_transform(df_hours)
    #     dump(scaler, 'NN/scaler.joblib')
        
    #     # 将归一化后的数据转换为DataFrame
    #     df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)
        
    #     # 合并
    #     df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
    #     print(df_pivot_clean.head)
    #     df_pivot_clean.to_csv('NN/scenic_data.csv', index=False)
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, count(*) as count
        FROM order_user_gate_rel g WHERE g.create_time BETWEEN '2024-07-01' and '2025-01-01' GROUP BY date
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # 合并天气数据
        self.weather_df['date'] = pd.to_datetime(self.weather_df['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(df, self.weather_df, on='date')
        df_pivot.set_index('date', inplace=True)
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month  # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        # 对星期几和月份、天气和风、风向进行独热编码
        # df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli', 'fengxiang'], dtype=int)
        # 对温度进行类型转换
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('°', '').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('°', '').astype(int)
        # 归一化入园数
        scaler = MinMaxScaler()
        feature = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(feature)
        dump(scaler, 'timing/scaler.joblib')
        # 归一化天气
        weather_features = ['bWendu', 'yWendu']
        df_pivot[weather_features] = scaler.fit_transform(df_pivot[weather_features])
        dump(scaler, 'timing/weather_scaler.joblib')

        df_pivot.to_csv('timing/scenic_data.csv', index=False)

        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()
    # w = WeatherUtils()
    # w.get_data()
    